package bbs.util;

import java.io.IOException;
import java.io.InputStream;
// java 反射包 java.lang.reflect
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import bbs.bean.Category;

public class DBHelper {

	static {
		try {

			// 读入配置文件 Properties Map集合 ==》 读入配置文件
			Properties props = new Properties();
			// 定义输入流 ClassLoader 类加载器
			InputStream in = DBHelper.class.getClassLoader()
					// conn.properties 必须在项目根目录
					.getResourceAsStream("conn.properties");
			props.load(in);
			url = props.getProperty("url");
			user = props.getProperty("user");
			pwd = props.getProperty("pwd");
			String driver = props.getProperty("driver");
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException("数据库驱动加载失败", e);
		} catch (IOException e) {
			throw new RuntimeException("配置文件读入失败！", e);
		}
	}

	private static String url;
	private static String user;
	private static String pwd;

	public static Connection openConnection() throws SQLException {
		Connection conn = DriverManager.getConnection(url, user, pwd);
		return conn;
	}
	
	/**
	 * List<Map<Stirng,Object>>集合
	 * @param sql
	 * @param params
	 * @return
	 */
	public static List<Map<String, Object>> query(String sql, Object... params) {
		System.out.println("SQL: " + sql);
		System.out.println("参数: " + java.util.Arrays.toString(params));
		Connection conn = null;
		try {
			conn = openConnection();
			// 获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			ResultSet rs = ps.executeQuery();
			// 获取结果集元数据对象
			ResultSetMetaData rsmd = rs.getMetaData();
			// 创建返回结果对象
			List<Map<String, Object>> ret = new ArrayList<>();
			while (rs.next()) {
				// 创建 map 集合
				Map<String, Object> row = new HashMap<String, Object>();
				// 获取每一个字段值, 设置到一个map中
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					String columnName = rsmd.getColumnName(i + 1);
					Object columnValue = rs.getObject(columnName);
					row.put(columnName, columnValue);
				}
				// 将 map 添加到 ret 中
				ret.add(row);
			}
			return ret;
		} catch (SQLException e) {
			throw new RuntimeException("执行SQL语句失败!", e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败", e);
			}
		}
	}

	/**
	 * 定义泛型方法
	 * list<>	
	 */
	public static <T> List<T> query(String sql, Class<T> cls, Object... params) {
		System.out.println("SQL："+ sql);
		System.out.println("参数：" + java.util.Arrays.toString(params));
		Connection conn = null;
		try {
			conn = openConnection();
			// 获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			// 执行语句
			ResultSet rs = ps.executeQuery();
			List<T> ret = new ArrayList<T>();
			// 元数据对象
			ResultSetMetaData rsmd = rs.getMetaData();
			for (; rs.next();) {
				// Java 黑科技 —— 反射
				T t = cls.newInstance(); // 创建对象 ==》 new XXX(); JavaBean
				// 遍历所有的的字段
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					
					//获取字段名
					String columnName = rsmd.getColumnName(i+1);
					//转成java的属性名
					String feildName = toJavaName(columnName);
					// 根据列名获取类的属性对象
					Field f = cls.getDeclaredField(feildName);
					//开启访问全选private属性 可以访问
					f.setAccessible(true);
					// 报错！！
					Object value = null;
					if(f.getType().equals(Integer.class)) {
						value = rs.getInt(i+1);
					} else if( f.getType().equals(Long.class)) {
						value = rs.getLong(i+1);
					} else if( f.getType().equals(String.class)) {
						value = rs.getString(i+1);
					/**
					 * 其他数据类型请自行完成
					 */
					}else {
						value = rs.getObject(i+1);
					}
					
					f.set(t, value);
				}
				ret.add(t);
			}
			return ret;
		} catch (Exception e) {
			throw new RuntimeException("sql执行失败", e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败", e);
			}
		}
	}
	
	/**
	 * 只查询一个记录，返回一个对象， 
	 * 	例如： 根据主键查询 select * from category where cid = 1
	 * @param <T>
	 * @param sql
	 * @param cls
	 * @param id
	 * @return
	 */
	public static <T> T queryById(String sql, Class<T> cls, Object id) {
		//List<T> list = query(sql, cls, id);
		//return list.isEmpty()? null : list.get(0);
		/**
		 *  1. query 查询胡数据大小没有限制
		 *  2. 多于一条的记录，应该要报错
		 */
		System.out.println("SQL："+ sql);
		System.out.println("参数：" +id);
		Connection conn = null;
		try {
			conn = openConnection();
			// 获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			ps.setObject(1, id);
			// 执行语句
			ResultSet rs = ps.executeQuery();
			T ret = null;
			// 元数据对象
			ResultSetMetaData rsmd = rs.getMetaData();
			System.out.println(rs);
			if(rs.next()) {
				// Java 黑科技 —— 反射
				ret = cls.newInstance(); // 创建对象 ==》 new XXX(); JavaBean
				// 遍历所有的的字段
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					
					String columnName = rsmd.getColumnName(i+1);
					//转成java的属性名
					String feildName = toJavaName(columnName);
					// 根据列名获取类的属性对象
					Field f = cls.getDeclaredField(feildName);
					// 开启访问全选 private属性 可以访问
					f.setAccessible(true);
					// 报错！！
					Object value = null;
					if(f.getType().equals(Integer.class)) {
						value = rs.getInt(i+1);
					} else if( f.getType().equals(Long.class)) {
						value = rs.getLong(i+1);
					} else if( f.getType().equals(String.class)) {
						value = rs.getString(i+1);
					
					/**
					 * 其他数据类型请自行完成
					 */
					} else {
						value = rs.getObject(i+1);
					}
					
					f.set(ret, value);
				}
				// 取出改行数据的值
				if(rs.next()) {
					throw new RuntimeException("结果集大于1！");
				} else {
					return ret;
				}
			} else {
				throw new RuntimeException("没有该数据！");
			}
		} catch (Exception e) {
			throw new RuntimeException("sql执行失败", e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败", e);
			}
		}
	}
	
	/**
	 * 只查询一个值， 
	 * 	例如： 根据统计查询    select count(*) from category
	 * 
	 * 许林岗提交
	 */
	public static <T>Object queryValue(String sql, Class<T> cls, Object...params) {
		System.out.println("SQL："+ sql);
		System.out.println("参数：" + java.util.Arrays.toString(params));
		Connection conn = null;
		try {
			conn = openConnection();
			// 获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			for (int i =0;i<params.length;i++) {
				ps.setObject(i+1,params[i]);
			}
			// 执行语句
			ResultSet rs = ps.executeQuery();
			return rs.next()?rs.getObject(1):null;
					
		} catch (Exception e) {
			throw new RuntimeException("sql执行失败", e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败", e);
			}
		}
		
	}
	/**
	 * 修改数据方法
	 */
	public static int update(String sql , Object...params){
		System.out.println("SQL:" + sql);
		System.out.println("参数：" + java.util.Arrays.toString(params));
		Connection conn = null;
		
		try {
			conn = openConnection();
			//获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			//设置参数
			for ( int i= 0;i<params.length;i++){
				ps.setObject(i+1 , params[i]);
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException("sql执行失败！",e);
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败",e);
			}
		}

	
		
	}
	/**
	 * 设置参数
	 * @param pstmt
	 * @param params
	 * @throws SQLException
	 */
/*	private void setParams(PreparedStatement pstmt, List<Object> params) throws SQLException {
		if(null!=params && params.size()>0){
			for(int i=0;i<params.size();i++){
				pstmt.setObject(i+1, params.get(i));
			}
		}
		
	}
	public int update(List<String> sqls,List<List<Object>> params) throws SQLException{
		int result=0;
		Connection conn = null;
		PreparedStatement pstmt=null;
		try{
			conn=openConnection();
			conn.setAutoCommit(false);//设置禁用事物自动提交
			if(null!=sqls && sqls.size()>0){
				for(int i=0;i<sqls.size();i++){
					String sql=sqls.get(i);//获取sql语句
					List<Object> list=params.get(i);//获取对应参数集合
					pstmt=conn.prepareStatement(sql);
					setParams(pstmt, list);
					result=pstmt.executeUpdate();
					if(result<=0){
						conn.rollback();
						return result;
					}
				}
				conn.commit();
			}
		}catch(SQLException e){
			conn.rollback();
			result=0;
			e.printStackTrace();
		}finally{
			conn.setAutoCommit(true);
			conn.close();
		}
		return result;
	}
	*/
	/**
	 * 将数据库字段的名称转成java 驼峰命名方式
	 * 
	 */
	private static String toJavaName(String columnName) {
		//先将字符串全部转成小写
		columnName = columnName.toLowerCase();
		//判断是否包含下划线
		while(columnName.contains("_")) {
			//查找下划线
			int i = columnName.indexOf("_");
			//获取下划线后面的字符
			char c = columnName.charAt(i+1);
			//转成大写
			c = Character.toUpperCase(c);
			//替换小写字符
			columnName = columnName.substring(0,i) + c + columnName.substring(i+2);
		}
		return columnName;
		//
	}
	
	
	public static void main(String[] args) throws SQLException {
		System.out.println(openConnection());

		String sql = "insert into user(uname,upwd) values(?,?)";

		update(sql,"css","123");

	}

}
